/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DAO;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import java.util.Properties;
import javax.swing.table.DefaultTableModel;

/**
 *
 * @author Nguyen Phi Hung
 */
public class DataAccess {
    protected static String sUsername;
    protected static String sPassword;
    protected static String sUrl;
    protected static Connection connection = null;
    
   public static boolean loadDBConnectionInfo(String sFileName) throws FileNotFoundException, IOException {

        Properties prop = null;
        prop = new Properties();
        String fileName = sFileName;
        InputStream is = new FileInputStream(fileName);
        prop.load(is);

        sUsername = prop.getProperty("db.username");
        sPassword = prop.getProperty("db.password");
        sUrl = prop.getProperty("db.url");

        return true;
    }

    public static boolean loadDBConnectionInfo() throws FileNotFoundException, IOException {

        Properties prop = null;
        prop = new Properties();
        String fileName = "QL_SO_TIET_KIEM.properties";
        InputStream is = new FileInputStream(fileName);
        prop.load(is);

        sUsername = prop.getProperty("db.username");
        sPassword = prop.getProperty("db.password");
        sUrl = prop.getProperty("db.url");

        return true;
    }

    public static boolean openConnection() throws ClassNotFoundException, SQLException {

        Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        connection = DriverManager.getConnection(sUrl, sUsername, sPassword);

        return true;
    }

    public static boolean closeConnection() throws SQLException {

        if (connection != null) {
            connection.close();
        }

        return true;
    }
    
    public static DefaultTableModel  execQuery(String sSQL) throws ClassNotFoundException, SQLException {

        openConnection();
        Statement statement;
        statement = connection.createStatement();
        
        
        ResultSet rs = statement.executeQuery(sSQL);
        
        ResultSetMetaData metaData = rs.getMetaData();
        int totalColumn = metaData.getColumnCount();
        Object[] dataRow = new Object[totalColumn];
        DefaultTableModel table = new DefaultTableModel();
        if(rs!= null)
        {
            for(int i=1;i<=totalColumn;i++)
            {
                table.addColumn(metaData.getColumnName(i));
            }
            while(rs.next())
            {
                for(int i=1;i<=totalColumn;i++)
                {
                    dataRow[i-1] = rs.getObject(i);
                }
                table.addRow(dataRow);
            }

        }
        rs.close();
        closeConnection();

        return table;
    }
    
    private static int getNumOfParasOfProc(Connection con, String procName) throws SQLException {
        DatabaseMetaData dbMetaData = con.getMetaData();
        ResultSet rs = dbMetaData.getProcedureColumns(con.getCatalog(),
                null,
                procName.toUpperCase(),
                "%");

        int i = 0;
        while (rs.next()) {
            i++;
        }
        return i;
    }

    private static String getProcParaFormat(int numPara) {
        String s = "";
        for (int i = 0; i < numPara; i++) {
            s += "?,";
        }
        if (s.endsWith(",")) {
            s = s.substring(0, s.length() - 1);
        }
        return s;
    }
}
